package MySql;

import java.sql.*;
import java.util.ArrayList;

public class cartManager {
    public static String displayAllRows() throws SQLException {
        String sql="SELECT * FROM cart";
        ResultSet rs=null; //结果集
        try (
                Connection conn=DBUtils.getConnection();
                Statement st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            rs=st.executeQuery(sql); //返回结果集
            rs.last();//指针到最后一条记录
            int nRows=rs.getRow();//返回记录数
            if (nRows==0) {
                System.out.println("没有找到满足查询条件的记录！\n");
                return "没有找到满足查询条件的记录！\n";
            }else {
                rs.beforeFirst(); //指针到第一条记录之前
                StringBuilder buffer=new StringBuilder(); //动态字符串
                while (rs.next()) { //遍历记录集
                    buffer.append(rs.getDouble("content")).append(",");
                    buffer.append(rs.getString("total")).append(",");
                    buffer.append(rs.getString("owner")).append("。");
                }//end while
//                System.out.println(buffer.toString());
                return buffer.toString();
            }//end if
        }catch (SQLException ex) {
        }finally {
            if (rs!=null) rs.close();
        }//end try
        return "";
    }//end displayAllRows

    public static ArrayList<Cart> displayAllCart() throws SQLException {
        String sql="SELECT * FROM cart";
        ResultSet rs=null; //结果集
        try (
                Connection conn=DBUtils.getConnection();
                Statement st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            rs=st.executeQuery(sql); //返回结果集
            rs.last();//指针到最后一条记录
            int nRows=rs.getRow();//返回记录数

            System.out.println(nRows);

            if (nRows==0) {
                System.out.println("没有找到满足查询条件的记录！\n");
                return null;
            }else {
                ArrayList<Cart> carts=new ArrayList<Cart>();
                rs.beforeFirst(); //指针到第一条记录之前
                while (rs.next()) { //遍历记录集
                    Double total=rs.getDouble("total");
                    String content=rs.getString("content");
                    String owner = rs.getString("owner");
                    Cart cart=new Cart(owner,content,total);
                    carts.add(cart);
                }//end while
//                System.out.println(buffer.toString());
                return carts;
            }//end if
        }catch (SQLException ex) {
        }finally {
            if (rs!=null) rs.close();
        }//end try
        return null;
    }//end displayAllRows

    //根据id查找记录
    public static Cart getRowById(String owner) throws SQLException {
        String sql="SELECT * FROM cart WHERE owner=?";
        ResultSet rs=null; //结果集
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            st.setString(1, owner); //设置参数
            rs=st.executeQuery(); //返回结果集
            if (rs.next()) { //找到
                Cart bean=new Cart(); //定义用户数据实体
                bean.setTotal(rs.getDouble("total"));
                bean.setContent(rs.getString("content"));
                bean.setOwner(rs.getString("owner"));
                return bean; //返回用户数据实体
            }else { //没找到
                return null;
            }
        } catch (SQLException ex) {
            return null;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }//end getRowById
    //注册用品
    public static boolean registerCart(Cart bean) throws SQLException{
        if (testId(bean.getOwner())) return false;//如果用户存在，则注册失败
        String sql="INSERT INTO cart(total,content,owner) VALUES (?,?,?)";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            //设置参数
            System.out.println(bean.getTotal());
            System.out.println(bean.getContent());
            System.out.println(bean.getOwner());

            st.setDouble(1,bean.getTotal());
            st.setString(2,bean.getContent());
            st.setString(3,bean.getOwner());

            int affected=st.executeUpdate();
            System.out.println(affected);
            return affected==1; //注册成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
        }//end try
    }//end registerUser
    //用户登录
    //对比账号和答案1
//    public static boolean passfind(Goods bean) throws SQLException{
//        String sql="SELECT * FROM goods WHERE id=? AND kind=? AND truename=?";
//        ResultSet rs=null;
//        try (
//                Connection conn=DBUtils.getConnection();
//                PreparedStatement st=conn.prepareStatement(sql,
//                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//        ){
//            //设置参数
//            st.setString(1,bean.getUsername());
//            st.setString(2, bean.getEmail());
//            st.setString(3, bean.getTrueName());
//            rs=st.executeQuery(); //返回结果集
//            return rs.next(); //登录成功或失败
//        } catch (SQLException ex) {
//            return false;
//        }finally {
//            if (rs!=null) rs.close();
//        }//end try
//    }

    public static boolean testId(String owner) throws SQLException{
        String sql="SELECT * FROM cart WHERE owner=? ";
        ResultSet rs=null;
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            //设置参数
            st.setString(1,owner);

            rs=st.executeQuery(); //返回结果集
            return rs.next(); //登录成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }

    //    public static boolean userLogin(User bean) throws SQLException {
//        String sql="SELECT * FROM usermember WHERE username=? AND passwd=?";
//        ResultSet rs=null;
//        try (
//                Connection conn=DBUtils.getConnection();
//                PreparedStatement st=conn.prepareStatement(sql,
//                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//        ){
//            //设置参数
//            st.setString(1,bean.getUsername());
//            st.setString(2, bean.getPasswd());
//            rs=st.executeQuery(); //返回结果集
//            return rs.next(); //登录成功或失败
//        } catch (SQLException ex) {
//            return false;
//        }finally {
//            if (rs!=null) rs.close();
//        }//end try
//    }//end userLogin
    //修改用户
    public static boolean updateCart(Cart bean) {
        String sql="UPDATE cart SET total= ? , content=? WHERE owner=?";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            //设置参数
//            st.setString(1, bean.getId());
            st.setDouble(1, bean.getTotal());
            st.setString(2, bean.getContent());
            st.setString(3,bean.getOwner());

            int affected=st.executeUpdate();
            return affected==1; //修改成功或失败
        } catch (SQLException ex) {
            return false;
        }//end try
    }//end updateUser
    //删除用户
    public static boolean deleteCart(String owner) {
        String sql="DELETE FROM cart WHERE owner=?";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            st.setString(1,owner);
            int affected=st.executeUpdate();
            return affected==1; //删除成功或失败
        } catch (SQLException ex) {
            return false;
        }//end try
    }//end updateUser
}
